package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EmpDao {
	static Connection con = null;
	static Statement stm = null;
	static PreparedStatement pst=null;
	static String sql = null;
	static ResultSet rs=null;
	
	public static void main(String[] args) throws Exception {
//		saveTest();
//		delete(1);
		updateTest();
//		System.out.println(findById(2));
		findAllTest();
	}
	private static void saveTest() throws Exception {
		Emp heantai = new Emp(1,"heantai",10000.0,new Date(),new Dept(1,"IT"));
		Emp cungui = new Emp(2,"heantai",10000.0,new Date(),new Dept(1,"IT"));
		save(heantai);
	}
	public static void save(Emp emp) throws Exception{
		try{
			con=DBUtil.open();
			sql="insert into hh_emp (id,name,sal,hiredate,deptid) values(?,?,?,?,?)";
			pst=con.prepareStatement(sql);
			pst.setInt(1, emp.id);
			pst.setString(2, emp.name);
			pst.setDouble(3, emp.sal);
			pst.setDate(4, new java.sql.Date(emp.hireDate.getTime()));
			pst.setInt(5, emp.dept.id);
			pst.executeUpdate();
		}finally{
			DBUtil.close(con, pst, null);
		}
	}
	public static void delete(int id) throws Exception{
		try{
			con=DBUtil.open();
			sql="delete from hh_emp where id = ?";
			pst=con.prepareStatement(sql);
			pst.setInt(1, 1);
			pst.executeUpdate();
		}finally{
			DBUtil.close(con, pst, null);
		}
	}
	public static void updateTest() throws Exception{
		Emp cungui = new Emp(2,"cungui",20000.0,new Date(),new Dept(2,"pingguan"));
		update(cungui);
	}
	private static void update(Emp emp) throws Exception {
		try{
			con=DBUtil.open();
			sql="update hh_emp set name=?,sal=?,hiredate=?,deptid=? where id=2";
			pst=con.prepareStatement(sql);
			pst.setString(1, emp.name);
			pst.setDouble(2, emp.sal);
			pst.setDate(3,new java.sql.Date(emp.hireDate.getTime()));
			pst.setInt(4, emp.dept.id);
			pst.executeUpdate();
		}finally{
			DBUtil.close(con, pst, null);
		}
	}
	public static Emp findById(int id) throws Exception{
		try{
			con=DBUtil.open();
			//select e.id eid from hh_emp 给字段取别名
			sql="select * from hh_emp e left join hh_dept d on e.id=? and e.deptid=d.id";
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			
			Emp e = null;
			if(rs.next()){
				e = new Emp();
				e.setId(id);
				e.setName(rs.getString("name"));
				e.setSal(rs.getDouble("sal"));
				e.setHireDate(rs.getTimestamp("hiredate"));
				
				int deptid=rs.getInt(6);
				if(!rs.wasNull()){//判断刚刚取出的字段是否是null
					e.setDept(new Dept(rs.getInt(6),rs.getString(7)));
				}
			}
			return e;
		}finally{
			DBUtil.close(con, pst, rs);
		}
	}
	public static void findAllTest() throws Exception{
		List<Emp> list = findAll();
		for (Emp e : list) {
			System.out.println(e);
		}
	}
	public static List<Emp> findAll() throws Exception{
		List<Emp> list = new ArrayList<Emp>();
		try{
			con=DBUtil.open();
			sql="select * from hh_emp e left join hh_dept d on d.id=e.deptid";
			pst=con.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()){
				Emp e = new Emp();
				e.setId(rs.getInt("id"));
				e.setName(rs.getString("name"));
				e.setSal(rs.getDouble("sal"));
				e.setHireDate(rs.getTimestamp("hiredate"));
				int deptid=rs.getInt(6);
				if(!rs.wasNull()){//判断刚刚取出的字段是否是null
					e.setDept(new Dept(rs.getInt(6),rs.getString(7)));
				}
				list.add(e);
			}
		}finally{
			DBUtil.close(con, pst, rs);
		}
		return list;
	}
}
